﻿using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using ProjectManageSystemApi.Models;
using ProjectManageSystemApi.Services;
using ProjectManageSystemApi.Utilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace ProjectManageSystemApi.Controllers
{
    [Authorize]
    [Route("api/[controller]")]
    [ApiController]
    [ApiExplorerSettings(GroupName = Grouping.GroupName_v1)]
    public class ProjectInfoController : Controller
    {
        readonly IProjectInfoServices _project;
        readonly IUserServices _user;
        readonly IRoleAuthServices _roleAuth;
        readonly IProjectItemInfoServices _projectItem;
        readonly IFreeSql _freeSql;

        public ProjectInfoController(IProjectInfoServices project, IUserServices user, IRoleAuthServices roleAuth, IProjectItemInfoServices projectItem, IFreeSql freeSql)
        {
            this._project = project;
            this._user = user;
            this._roleAuth = roleAuth;
            this._projectItem = projectItem;
            this._freeSql = freeSql;
        }

        [HttpPost, Route("Create")]
        public ApiResult Create([FromBody] ProjectInfoDto projectInfo)
        {
            if (projectInfo == null
                || string.IsNullOrWhiteSpace(projectInfo.ProjectName))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED);
            }
            string token = Request.GetToken();
            if (string.IsNullOrWhiteSpace(token))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED);
            }
            if (!_user.GetUserIdByToken(token, out long userId))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED);
            }
            projectInfo.CreateUser = userId;
            projectInfo.Id = 0;
            if (_project.HasExistName(projectInfo.ProjectName))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PROJECT_NAME_EXIST);
            }
            if (!_project.Create(projectInfo))
            {
                return ApiResultStatus.Fail(ApiResultCodes.OPERATION_FAILED);
            }
            return ApiResultStatus.Success();
        }

        [HttpPost, Route("Update")]
        public ApiResult Update([FromBody] ProjectInfoDto projectInfo)
        {
            if (projectInfo == null
                || projectInfo.Id == 0
                || string.IsNullOrWhiteSpace(projectInfo.ProjectName))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED);
            }
            if (_project.HasExistName(projectInfo.ProjectName, projectInfo.Id))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PROJECT_NAME_EXIST);
            }
            // 判断该工程状态
            if (!_project.HasExistId(projectInfo.Id))
            {
                return ApiResultStatus.Fail(ApiResultCodes.DATA_NOT_EXIST);
            }
            if (_project.IsFinish(projectInfo.Id))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PROJECT_FINISH);
            }
            if (!_project.Update(projectInfo))
            {
                return ApiResultStatus.Fail(ApiResultCodes.OPERATION_FAILED);
            }
            return ApiResultStatus.Success();
        }

        [HttpPost, Route("Get")]
        public ApiResult Query(PageBo<ProjectInfoParams> pageBo)
        {
            if (pageBo == null)
            {
                pageBo = new PageBo<ProjectInfoParams>();
            }
            if (pageBo.Param == null)
            {
                pageBo.Param = new ProjectInfoParams();
            }

            string token = Request.GetToken();
            if (string.IsNullOrWhiteSpace(token))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED);
            }
            if (!_user.GetUserIdByToken(token, out long userId))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED);
            }
            Models.PageVo<List<ProjectInfoDto>> result;

            // 判断是否拥有查看所有工程的权限
            if (_roleAuth.IsShowAllProject(userId))
            {
                result = _project.GetAll(pageBo);
            }
            else
            {
                result = _project.GetByUserId(userId, pageBo);
            }
            return ApiResultStatus.Success(result);
        }

        [HttpPost, Route("Delete")]
        public ApiResult Delete(int projectId)
        {
            if (projectId == 0)
            {
                return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED);
            }
            // 判断该工程当前是否为删除状态
            if (!_project.HasExistId(projectId))
            {
                return ApiResultStatus.Fail(ApiResultCodes.DATA_NOT_EXIST);
            }
            // 判断该工程是否已完成，完成状态下不允许修改
            if (_project.IsFinish(projectId))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PROJECT_FINISH);
            }
            // 修改状态值
            if (!_project.UpdateState(projectId, Enums.ProjectInfoState.Delete))
            {
                return ApiResultStatus.Fail(ApiResultCodes.OPERATION_FAILED);
            }
            return ApiResultStatus.Success();
        }

        [HttpPost, Route("Finish")]
        public ApiResult Finish(int projectId)
        {
            if (projectId == 0)
            {
                return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED);
            }
            // 判断该工程当前是否为删除状态
            if (!_project.HasExistId(projectId))
            {
                return ApiResultStatus.Fail(ApiResultCodes.DATA_NOT_EXIST);
            }
            // 判断该工程是否已完成，完成状态下不允许修改
            if (_project.IsFinish(projectId))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PROJECT_FINISH);
            }
            // 修改状态值
            if (!_project.UpdateState(projectId, Enums.ProjectInfoState.Finish))
            {
                return ApiResultStatus.Fail(ApiResultCodes.OPERATION_FAILED);
            }
            return ApiResultStatus.Success();
        }

        [HttpPost, Route("ExcelUpload")]
        public ApiResult ExcelUpload(IFormFile file)
        {
            if (file == null || file.Length <= 0)
            {
                return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED);
            }
            //if (file.ContentType != "application/vnd.ms-excel"
            //    && file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")  // 2007 版本的 excel
            //{
            //    return ApiResultStatus.Success(ApiResultCodes.EXCEL_UPLOAD_FILE_TYPE_UNKNOWS);
            //}
            IWorkbook workbook = null;
            try
            {
                string suffix = Path.GetExtension(file.FileName);
                if (suffix.EndsWith(".xlsx", StringComparison.CurrentCultureIgnoreCase))
                {
                    workbook = new XSSFWorkbook(file.OpenReadStream());
                }
                else if (suffix.EndsWith(".xls", StringComparison.CurrentCultureIgnoreCase))
                {
                    workbook = new HSSFWorkbook(file.OpenReadStream());
                }
                else
                {
                    return ApiResultStatus.Fail(ApiResultCodes.OPERATION_FAILED, "读取excel失败");
                }
            }
            catch (Exception)
            {
                return ApiResultStatus.Fail(ApiResultCodes.OPERATION_FAILED, "读取excel失败");
            }
            JArray resultData = new JArray();
            for (int sheetIndex = 0, len = workbook.NumberOfSheets; sheetIndex < len; sheetIndex++)
            {
                JObject sheetObject = new JObject();
                var sheet = workbook.GetSheetAt(sheetIndex);
                if (sheet.SheetName == "KING" || sheet.SheetName == "编制说明" || sheet.SheetName == "封面" || sheet.SheetName == "汇总表")
                {
                    continue;
                }
                string projectName = sheet.GetRow(1)?.GetCell(0)?.StringCellValue?.Replace("工程名称：", "");
                sheetObject.Add("SheetName", sheet.SheetName);
                sheetObject.Add("ProjectName", projectName);

                DataTable dt = new DataTable();
                var headerRow = sheet.GetRow(2);// 第三行是表头
                var mapDatas = ExcelMapDatas.ToArray().Copy();

                int lastCellNum = headerRow?.LastCellNum ?? 0;
                int lastRowNum = sheet.LastRowNum;
                bool rowDataNotNull = false;
                string cellValue;
                // 表头列
                for (int columnIndex = 0, emtryIndex = 0; columnIndex < lastCellNum; columnIndex++)
                {
                    var columnName = headerRow.GetCell(columnIndex)?.GetValue()?.Trim();
                    if (columnName.IsNullOrEmpty())
                    {
                        columnName = "列" + (columnIndex + 1);
                    }
                    var map = mapDatas.FirstOrDefault(x => columnName.StartsWith(x.ChineseName) && x.ColumnIndex < 0); // 考虑字符串接近算法
                    if (map == null) 
                    {
                        map = new ExcelMap()
                        {
                            DataKey = "Item" + (++emtryIndex) // 用默认名称代替
                        };
                        mapDatas.Add(map);
                    }
                    map.ChineseName = columnName;
                    map.ColumnIndex = columnIndex;

                    dt.Columns.Add(map.DataKey, typeof(string));
                }
                foreach (var map in mapDatas.Where(x=>x.ColumnIndex < 0)) 
                {
                    dt.Columns.Add(map.DataKey, typeof(string));
                }
                // 表数据
                for (int rowIndex = 3; rowIndex < lastRowNum; rowIndex ++) 
                {
                    rowDataNotNull = false;
                    DataRow dr = dt.NewRow();
                    var row = sheet.GetRow(rowIndex);
                    if (row == null) 
                    {
                        continue;
                    }
                    foreach (var map in mapDatas.Where(x => x.ColumnIndex >= 0))
                    {
                        cellValue = row.GetCell(map.ColumnIndex).GetValue();

                        if (string.IsNullOrWhiteSpace(cellValue))
                        {
                            continue;
                        }
                        else if (!map.IsItemName)
                        {
                            dr[map.DataKey] = cellValue;
                            rowDataNotNull = true;
                            map.NotAllNull = true;
                            continue;
                        }
                        int splitIndex = cellValue.IndexOfAny(new Char[] { '\t', '\n', '\r' });
                        var descMap = mapDatas.FirstOrDefault(x => x.IsItemDesc && x.ColumnIndex < 0);
                        if (splitIndex < 0 || descMap == null)
                        {
                            dr[map.DataKey] = cellValue;
                            rowDataNotNull = true;
                            map.NotAllNull = true;
                            continue;
                        }
                        dr[map.DataKey] = cellValue.Substring(0, splitIndex);
                        dr[descMap.DataKey] = cellValue.Substring(splitIndex, cellValue.Length - splitIndex).TrimStart(new char[] { '\t', '\n', '\r' });
                        rowDataNotNull = true;
                        map.NotAllNull = true;
                    }
                    if (rowDataNotNull)  
                    {
                        dt.Rows.Add(dr);
                    }
                }
                foreach (var m in mapDatas.Where(x => !x.IsSpecial && !x.NotAllNull)) 
                {
                    dt.Columns.Remove(m.DataKey);
                }
                mapDatas.RemoveAll(x => !x.IsSpecial && !x.NotAllNull);

                sheetObject.Add("Header", JsonConvert.DeserializeObject<JArray>(JsonConvert.SerializeObject(mapDatas)));
                sheetObject.Add("Datas", JsonConvert.DeserializeObject<JArray>(JsonConvert.SerializeObject(dt))); // 
                resultData.Add(sheetObject);
            }
            return ApiResultStatus.Success(resultData);
        }

        [HttpPost, Route("ProjectSave")]
        public ApiResult ProjectSave([FromHeader(Name = "Authorization")] string auth, [FromBody] ExcelDataModel data)
        {
            // TODO:优化数据校验
            if (data == null || data.ProjectName.IsNullOrEmpty() || data.ItemDatas.Count <= 0)
            {
                return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED);
            }
            for (int i = 0, len = data.ItemDatas.Count; i < len; i++)
            {
                // 做一下必要的数据校验，因为前台没做
                var item = data.ItemDatas[i];
                if (item.ItemNo.IsNullOrEmpty())
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，项目单号为空");
                }
                if (item.ItemNo.Length > 100)
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，项目单号超出100字符");
                }

                if (item.ItemName.IsNullOrEmpty())
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，项目名称为空");
                }
                if (item.ItemName.Length > 100)
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，项目名称超出100字符");
                }

                if (item.ItemDesc != null && item.ItemDesc.Length > 200)
                {
                    item.ItemDesc.Substring(0, 200);
                }

                if (item.BudgetDosage.IsNullOrEmpty())
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，预算用量为空");
                }
                if (!double.TryParse(item.BudgetDosage, out double bd))
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，预算用量不是数字型，无法使用");
                }
                item.BudgetDosageValue = bd;

                if (item.BudgetAmount.IsNullOrEmpty())
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，预算单价为空");
                }
                if (!decimal.TryParse(item.BudgetAmount, out decimal ba))
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，预算单价不是数字型，无法使用");
                }
                item.BudgetAmountValue = ba;

                if (item.FactDosage.IsNullOrEmpty())
                {
                    item.FactDosage = "0";
                    // return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，实际用量为空");
                }
                if (!double.TryParse(item.FactDosage, out double fd))
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，实际用量不是数字型，无法使用");
                }
                item.FactDosageValue = fd;

                if (item.FactAmount.IsNullOrEmpty())
                {
                    item.FactAmount = "0";
                    // return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，实际单价为空");
                }
                if (!decimal.TryParse(item.FactAmount, out decimal fa))
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，实际单价不是数字型，无法使用");
                }
                item.FactAmountValue = fa;

                if (item.ItemUnit.IsNullOrEmpty())
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，单位为空");
                }
                if (item.ItemUnit.Length > 100)
                {
                    return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED, $"数据第{i + 1}行，单位超出100字符");
                }
            }
            // 获取当前UserId
            if (!_user.GetUserIdByToken(auth.ReplaceBearer(), out long userId))
            {
                return ApiResultStatus.Fail(ApiResultCodes.PARAMS_FAILED);
            }

            // 获取工程名称
            try
            {
                _freeSql.Transaction(() =>
                    {
                        var id = _project.AddAndGetIdByName(data.ProjectName, userId);
                        if (id <= 0)
                        {
                            throw new DbTranException("获取工程失败");
                        }
                        var r = _projectItem.Insert(id, data.ItemDatas, userId);
                        if (!r)
                        {
                            throw new DbTranException("获取项目失败");
                        }
                    });
                return ApiResultStatus.Success();
            }
            catch (Exception)
            {
                return ApiResultStatus.Fail(ApiResultCodes.OPERATION_FAILED);
            }
        }
        public class ExcelMap 
        {
            public string ChineseName { get; set; } // 中文名称
            public string PropName { get; set; } // 保存是实体列名称
            public string DataKey { get; set; } // 属性名称
            public int ColumnIndex { get; set; } = -1;// 列索引
            public string InputType { get; set; } // Js数据类型
            public bool IsSpecial { get; set; } // 是否是特殊列
            public bool IsItemName { get; set; } // 是否是项目名称
            public bool IsItemDesc { get; set; } // 是否是项目描述
            public bool NotAllNull { get; set; } // 数据并非全为空
        }
        public static ExcelMap[] ExcelMapDatas
        {
            get
            {
                return new List<ExcelMap>
                {
                    new ExcelMap { ChineseName = "序号",DataKey = "ItemNo", PropName = "ItemNo", InputType = "text", IsSpecial = true },
                    new ExcelMap { ChineseName = "项目名称",DataKey = "ItemName" , PropName = "ItemName", InputType = "text", IsItemName = true, IsSpecial = true },
                    new ExcelMap { ChineseName = "项目描述",DataKey = "ItemDesc", PropName = "ItemDesc", InputType = "text", IsSpecial = true, IsItemDesc = true },
                    new ExcelMap { ChineseName = "计量单位", DataKey = "ItemUnit" , PropName = "ItemUnit", InputType = "text", IsSpecial = true },
                    new ExcelMap { ChineseName = "清单工程数量", DataKey = "BudgetDosage" , PropName = "BudgetDosage", InputType = "number", IsSpecial = true },
                    new ExcelMap { ChineseName = "实际工程量", DataKey = "FactDosage", PropName = "FactDosage", InputType = "number", IsSpecial = true },
                    new ExcelMap { ChineseName = "单价", DataKey = "BudgetAmount" , PropName = "BudgetAmount", InputType = "number", IsSpecial = true },
                    new ExcelMap { ChineseName = "实际单价", DataKey = "FactAmount", PropName = "FactAmount", InputType = "number", IsSpecial = true },
                }.ToArray();
            }
        }
    }
}
